| MySQL | 您所在的位置：网站首页 › mysql drop multiple columns › MySQL |
MySQL
|
MySQL – Alter Table
Using the ALTER TABLE statement in MySQL, you can add, modify, or delete columns from a table. It is also possible to rename a table with the ALTER TABLE statement in MySQL. Add column in tableSyntaxAdding a column to a table in MySQL (using ALTER TABLE) is as follows: ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];table_name – To modify, enter the table name. new_column_name – To add a new column to the table, give it a name. column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified. ExampleThe following query can be used to add a column named “address” to the “employees” table: ALTER TABLE employees ADD COLUMN address VARCHAR(100);
Adding multiple columns to a MySQL table (with the ALTER TABLE statement) requires the following syntax: ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ], ADD new_column_name column_definition [ FIRST | AFTER column_name ], ... ;table_name – To modify, enter the table name. new_column_name – To add a new column to the table, give it a name. column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified. ExampleHere is an example of how to add multiple columns to a MySQL table using the ALTER TABLE command . ALTER TABLE employees ADD city varchar(40) NOT NULL AFTER last_name, ADD country varchar(35) NULL AFTER city;
In the ALTER TABLE example, the city and country columns are added to the employee table. The city field will be created as a varchar(40) NOT NULL column and will appear after the last_name column in the table. The country column will be created as a varchar(35) NULL column and will appear after the city column in the table. Modify column in tableSyntaxModifying a column in a MySQL table (using the ALTER TABLE statement) is as follows: ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ];table_name – To modify, enter the table name. new_column_name – To add a new column to the table, give it a name. column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified. ExampleHere’s an example of how to use the ALTER TABLE statement to modify a column in a MySQL table. ALTER TABLE employees MODIFY COLUMN salary INT;
Using the ALTER TABLE statement, you can modify multiple columns in a table in MySQL: SyntaxALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ], MODIFY column_name column_definition [ FIRST | AFTER column_name ], ... ;table_name – To modify, enter the table name. new_column_name – To add a new column to the table, give it a name. column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc). FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified. ExampleUsing the ALTER TABLE statement, we can modify multiple columns in a MySQL table. ALTER TABLE employees MODIFY last_name varchar(55) NULL AFTER first_name, MODIFY first_name varchar(30) NOT NULL;
Using the ALTER TABLE statement in MySQL, you can drop a column in a table as follows: SyntaxALTER TABLE table_name DROP COLUMN column_name;table_name – Modify the name of the table. column_name – Deletes the column from the table by its name. ExampleHere is an example of using the ALTER TABLE statement to remove a column from a MySQL table. ALTER TABLE employees DROP COLUMN address;
In MySQL, a column can be renamed by using the ALTER TABLE statement: ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ]Example ALTER TABLE employees CHANGE COLUMN city city_name varchar(20) NOT NULL;
In MySQL, you can rename a table by using the following syntax: ALTER TABLE table_name RENAME TO new_table_name;table_name – This is the table that needs to be renamed. new_table_name – Use this new table name. ExampleALTER TABLE employees RENAME TO staff;
|
This ALTER TABLE example will modify the column called last_name to be a data type of varchar(50) and force the column to allow NULL values.
【本文地址】